# coding:utf-8
import logging
import traceback

from script.mysql_lib import sql_conn
from script.mysql_lib.helps import format_insert_fields, format_update_fields


class MysqlBase:
    def __init__(self, table_name):
        self.table = table_name

    def insert_tpl(self, data):
        fields, padding = format_insert_fields(data)
        return "INSERT INTO `{table_name}` ({fields}) VALUES ({padding})".format(
            table_name=self.table, fields=fields, padding=padding
        )

    def insert_many_tpl(self, data):
        if not isinstance(data, list):
            return self.insert_tpl(data)

        fields, padding = format_insert_fields(data[0])
        padding = ','.join(map('({})'.format, [padding] * len(data)))
        return "INSERT INTO `{table_name}` ({fields}) VALUES {padding}".format(
            table_name=self.table, fields=fields, padding=padding
        )

    def replace_tpl(self, fields, values):
        return "REPLACE INTO `{table_name}` ({fields}) VALUES ({values})".format(
            table_name=self.table, fields=fields, values=values
        )

    def update_tpl(self, update_fields, where=None):
        sql = "UPDATE `{table_name}` SET {fields}".format(
            table_name=self.table, fields=format_update_fields(update_fields)
        )
        if where:
            sql += ' ' + where
        return sql

    def on_duplicate_update_tpl(self, data):
        return " on duplicate key update " + format_update_fields(data)

    def select_tpl(self, select_fields, where=None, sort=None, limit=None):
        sql = "SELECT {select_fields} FROM `{table_name}`".format(
            select_fields=','.join(map('`{}`'.format, select_fields)), table_name=self.table
        )
        if where:
            sql += ' ' + where
        if sort:
            sql += ' ' + sort
        if limit:
            sql += ' ' + limit

        return sql

    def delete_tpl(self, where):
        return "DELETE FROM `{}` ".format(self.table) + where

    def query(self, sql, values=None, debug=True):
        conn, cursor = self._execute(sql, values, debug)
        if cursor:
            try:
                return list(cursor.fetchall())
            finally:
                conn.close()

    def execute(self, sql, values=None, debug=True):
        conn, cursor = self._execute(sql, values, debug)
        if cursor:
            try:
                return cursor.rowcount, cursor.lastrowid
            finally:
                conn.close()

    def _execute(self, sql, values=None, debug=True):
        conn = sql_conn.get_connection()
        cursor = conn.cursor(buffered=True, dictionary=True)

        try:
            cursor.execute(sql, tuple(values) if values else None)
            return conn, cursor
        except Exception as e:
            conn.close()
            print(e)
            if debug:
                exc_str = traceback.format_exc()
                logging.error(exc_str)
                logging.error(sql)
                if values:
                    logging.error(values)
            return None, None
